# -!- coding: utf-8 -!-
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
#作者：cacho_37967865
#博客：https://blog.csdn.net/sinat_37967865
#文件：get_stock_lhb.py
#日期：2019-01-20
#备注：获取东方财富网的每天统计的股票龙虎榜:http://datapc.eastmoney.com/soft/stock/TradeDetail.aspx?date=2019-01-20,
      存入到数据库，lhbInfo_to_mysql()    
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

import requests
import time
import re
from pacong.stock.stock_config import db,cursor,headers
from pycacho.cachobase.logger import Logger
from pycacho.cachobase.chinese_workday import get_trade_day
logger = Logger("get_stock_lhb").get_log()


def createTable():
    sql1 = 'drop table if exists stock_lhbInfo,stock_lhbDetail,stock_lhb_exchange;'
    sql2 = 'create table stock_lhbInfo(lhb_date varchar(20),lhb_stNo varchar(10),lhb_stName varchar(10),lhb_Chg float,lhb_Trade float,lhb_buy float,lhb_buyRate float,lhb_sell float,lhb_sellRate float,lhb_reason varchar(100),' \
           'PRIMARY KEY (lhb_date,lhb_stNo,lhb_reason));'   # 股票龙虎榜
    sql3 = 'create table stock_lhbDetail(det_date varchar(20),det_stNo varchar(10),det_type varchar(20),det_No int,det_name varchar(50),det_inTrade float,det_outTrade float,det_sumTrade float,det_total float,' \
           'PRIMARY KEY (det_date,det_stNo,det_type,det_name));'  # 股票龙虎榜详情
    sql4 = 'create table stock_lhb_exchange(lhb_company varchar(20),lhb_exchange varchar(50),PRIMARY KEY (lhb_exchange));'  # 股票龙虎榜营业

    cursor.execute(sql1)
    cursor.execute(sql2)
    cursor.execute(sql3)
    cursor.execute(sql4)
    db.commit()


# 获取股票龙虎榜上榜信息
def get_lhbInfo(date):
    url = 'http://datapc.eastmoney.com/soft/stock/TradeDetail.aspx?date={0}'.format(date)
    resp = requests.get(url, headers=headers)
    logger.info('爬取的龙虎榜上榜信息为：%s', url)
    data = resp.text
    lhb_info = re.findall(r'eval\((.*)\)',data)[0].replace('[','').replace(']','')
    lhb_dict = eval(lhb_info)               # 字符串转化为字典类型
    #print(type(lhb_dict),lhb_dict)
    for info in lhb_dict:
        # print(info)
        lhb_stNo = info['SECURITYCODE'][0:6]          # 股票代码
        lhb_stName = info['SECURITYNAME']             # 股票名称
        lhb_Chg = round(float(info['CHGRADIO']),2)    # 当日涨跌幅(四舍五入)
        lhb_Trade = float(info['SUMMONEY'])           # 当日龙虎榜成交额
        lhb_buy = float(info['BMONEY'])               # 当日龙虎榜买入成交额
        lhb_buyRate = round(float(info['BRATE']),2)   # 当日龙虎榜买入成交占比
        lhb_sell = float(info['SMONEY'])              # 当日龙虎榜卖出成交额
        lhb_sellRate = round(float(info['SRATE']),2)  # 当日龙虎榜卖出成交占比
        lhb_reason = info['TYPE']                     # 当日上榜理由
        lhbInfo_to_mysql(date,lhb_stNo,lhb_stName,lhb_Chg,lhb_Trade,lhb_buy,lhb_buyRate,lhb_sell,lhb_sellRate,lhb_reason)
        lhbInfo_bak_to_mysql(date, lhb_stNo, lhb_stName, lhb_Chg, lhb_Trade, lhb_buy, lhb_buyRate, lhb_sell, lhb_sellRate,lhb_reason)
        get_lhbDetail(date, lhb_stNo)


# 股票龙虎榜上榜信息
def lhbInfo_to_mysql(lhb_date,lhb_stNo,lhb_stName,lhb_Chg,lhb_Trade,lhb_buy,lhb_buyRate,lhb_sell,lhb_sellRate,lhb_reason):
    sql = "insert into stock_lhbInfo (lhb_date,lhb_stNo,lhb_stName,lhb_Chg,lhb_Trade,lhb_buy,lhb_buyRate,lhb_sell,lhb_sellRate,lhb_reason) " \
          "values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s');" \
          % (lhb_date,lhb_stNo,lhb_stName,lhb_Chg,lhb_Trade,lhb_buy,lhb_buyRate,lhb_sell,lhb_sellRate,lhb_reason)
    try:
        cursor.execute(sql)
    except Exception as e:
        # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()
        logger.info('插入股票%s龙虎榜信息成功！',lhb_stNo)


# 股票龙虎榜上榜信息
def lhbInfo_bak_to_mysql(lhb_date, lhb_stNo, lhb_stName, lhb_Chg, lhb_Trade, lhb_buy, lhb_buyRate, lhb_sell,lhb_sellRate, lhb_reason):
    sql = "insert into stock_lhbinfo_bak (lhb_date,lhb_stNo,lhb_stName,lhb_Chg,lhb_Trade,lhb_buy,lhb_buyRate,lhb_sell,lhb_sellRate,lhb_reason) " \
            "values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s');" \
            % (lhb_date, lhb_stNo, lhb_stName, lhb_Chg, lhb_Trade, lhb_buy, lhb_buyRate, lhb_sell, lhb_sellRate,lhb_reason)
    try:
        cursor.execute(sql)
    except Exception as e:
        db.rollback()
        print(str(e))
    else:
        db.commit()
        logger.info('插入股票%s龙虎榜信息成功！', lhb_stNo)


# 获取股票龙虎榜上榜详细信息
def get_lhbDetail(date,lhb_stNo):
    url = 'http://datapc.eastmoney.com/soft/stock/StockDetail.aspx?code={0}&date={1}'.format(lhb_stNo,date)
    resp = requests.get(url, headers=headers)
    data = resp.text
    logger.info('爬取龙虎榜上榜详细信息为：%s', url)
    try:
        det_total = float(re.findall(r'成交金额：(\d.*)万元</li>',data)[0])*10000
        detail = re.findall(r'/soft/stock/TradeSearchHistory.aspx\?id=(.*)</td></tr>',data)
        ex_buy = detail[0].replace('(','').replace(')','')          # 买入金额最大的前5名(去除营业部的括号)
        #print(ex_buy)
        ex_info = re.findall(r'>([\u4e00-\u9fa5]+)</a>',ex_buy)    # 获取中文
        ex_Trade = re.findall(r'span class="\w*">(-*\d{,8}.\d{,2})</span>', ex_buy)
        #print(ex_info)
        #print(ex_Trade)
        for i in range(5):
            ex_company = ex_info[i].split('证券')[0]+'证券'
            det_name = ex_info[i]
            det_inTrade = float(ex_Trade[3*i])*10000
            det_outTrade = float(ex_Trade[3*i+1])*10000
            det_sumTrade = float(ex_Trade[3*i+2])*10000
            lhb_exchange_to_mysql(ex_company, det_name)
            lhbDetail_to_mysql(date,lhb_stNo,'买入前5',i+1,det_name, det_inTrade, det_outTrade, det_sumTrade,det_total)

        ex_sell = detail[1].replace('(','').replace(')','')        # 卖出金额最大的前5名
        #print(ex_sell)
        ex_info = re.findall(r'>([\u4e00-\u9fa5]+)</a>', ex_sell)  # 获取中文
        ex_Trade = re.findall(r'span class="\w*">(-*\d{,6}.\d{,2})</span>', ex_sell)
        #print(ex_info)
        #print(ex_Trade)
        for i in range(5):
            ex_company = ex_info[i].split('证券')[0]+'证券'
            det_name = ex_info[i]
            det_inTrade = float(ex_Trade[3 * i])*10000
            det_outTrade = float(ex_Trade[3 * i + 1])*10000
            det_sumTrade = float(ex_Trade[3 * i + 2])*10000
            lhb_exchange_to_mysql(ex_company,det_name)
            lhbDetail_to_mysql(date,lhb_stNo,'卖出前5', i + 1,det_name, det_inTrade, det_outTrade, det_sumTrade,det_total)
    except Exception as e:
        print(str(e))


# 股票龙虎榜上榜详细信息
def lhbDetail_to_mysql(det_date,det_stNo,det_type,det_No,det_name,det_inTrade,det_outTrade,det_sumTrade,det_total):
    sql = "insert into stock_lhbDetail (det_date,det_stNo,det_type,det_No,det_name,det_inTrade,det_outTrade,det_sumTrade,det_total) " \
          "values ('%s','%s','%s','%s','%s','%s','%s','%s','%s');" \
          % (det_date,det_stNo,det_type,det_No,det_name,det_inTrade,det_outTrade,det_sumTrade,det_total)
    try:
        cursor.execute(sql)
    except Exception as e:
        # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()
        logger.info('插入股票%s龙虎榜详细信息成功！',det_stNo)


# 股票龙虎榜上榜营业部信息
def lhb_exchange_to_mysql(lhb_company,lhb_exchange):
    sql = "insert into stock_lhb_exchange (lhb_company,lhb_exchange) values ('%s','%s');" % (lhb_company,lhb_exchange)
    try:
        cursor.execute(sql)
    except Exception as e:
        # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()
        logger.info('插入龙虎榜营业部%s信息成功！',lhb_exchange)


if __name__ == '__main__':
    cTime = time.time()
    td_day = get_trade_day(0, 't')
    get_lhbInfo(td_day)
    logger.info("总共耗时：%s ", time.time() - cTime)